package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.dao.InsAccountChangeDao;
import com.jiepos.mpos.common.entity.App_Ins_Account_ChangeEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 机构账户变动明细业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年11月6日 下午4:01:30
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class InsAccountChangeService extends BaseService {

	@Autowired
	private InsAccountChangeDao insAccountChangeDao;

	/**
	 * 获取机构账户变动明细数据
	 * 
	 * @param request
	 * @param pagination
	 * @param insAccountChangeEntity
	 * @param ins_name
	 * @param ins_cd
	 * @param cur_custom_login_id
	 * @param cur_custom_name
	 * @param cur_custom_no
	 * @return
	 */
	public Map getInsAccountChangeList(HttpServletRequest request, Pagination pagination,
			App_Ins_Account_ChangeEntity insAccountChangeEntity, String loc_dt_start, String loc_dt_end,
			String cur_custom_no, String cur_custom_name, String cur_custom_login_id, String ins_cd, String ins_name) {
		JSONObject common = getInsAccChangeSqlParams(loc_dt_start, loc_dt_end, cur_custom_no, cur_custom_name, cur_custom_login_id, ins_cd, ins_name);
		pagination.setQueryString(common.getString("sql"));
		pagination = super.getInsAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " order by t1.loc_dt desc";
		return insAccountChangeDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));

	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param insAccountChangeEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param cur_custom_no
	 * @param cur_custom_name
	 * @param cur_custom_login_id
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	public String exportInsAccountChangeLists(String fileName, HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, App_Ins_Account_ChangeEntity insAccountChangeEntity, String loc_dt_start,
			String loc_dt_end, String cur_custom_no, String cur_custom_name, String cur_custom_login_id, String ins_cd,
			String ins_name) {
		JSONObject common = getInsAccChangeSqlParams(loc_dt_start, loc_dt_end, cur_custom_no, cur_custom_name, cur_custom_login_id, ins_cd, ins_name);
		String sql = super.getInsAuthSql(request, common.getString("sql"), "t1", null);
		sql += " order by t1.loc_dt desc";
		List<Map> resultList = insAccountChangeDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("变动日期");
		headList.add("机构（收益人）");
		headList.add("发起方会员");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("唯一流水号");
		headList.add("变动金额");
		headList.add("备注");
		return ExcelUtil.writeExcel(fileName, "机构账户变动明细", headList, resultList);
	}

	private JSONObject getInsAccChangeSqlParams(String loc_dt_start, String loc_dt_end,
			String cur_custom_no, String cur_custom_name, String cur_custom_login_id, String ins_cd, String ins_name) {
		StringBuffer sqlSb = new StringBuffer();
		List<String> list = new ArrayList<>();
		sqlSb.append("select change_uuid as uuid, ");
		sqlSb.append("TO_CHAR(TO_DATE(t1.loc_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as loc_dt, ");
		sqlSb.append("t2.ins_cd||'-'||t2.ins_cn_nm as ins_name, ");
		sqlSb.append("CONCAT(RPAD (SUBSTR (t3.login_id, 1, 3),LENGTH (t3.login_id) - 4,'*'),SUBSTR (t3.login_id,LENGTH (t3.login_id) - 3,4))||'-'||t3.name as cur_custom, ");
		sqlSb.append(" t3.custom_no  as custom_no,");
		sqlSb.append("t1.service_no||'-'||t1.service_name as service_name, ");
		sqlSb.append("t1.param_no||'-'||t1.param_name as service_param_name, ");
		sqlSb.append("t1.loc_trace_no, ");
		sqlSb.append("to_char(t1.amount,'fm9999999990.00') as amount, ");
		sqlSb.append("t1.remarks ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_INS_ACCOUNT_CHANGE + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_uuid = t2.ins_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.cur_custom_uuid = t3.custom_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(loc_dt_start)) {
			Date date = DateUtil.strToDate(loc_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.loc_dt >= ? ");
			list.add(DateUtil.getAddDaysDate(new Date(), -31));
		}

		if (!StringUtils.isBlank(loc_dt_end)) {
			Date date = DateUtil.strToDate(loc_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(cur_custom_no)) {
			sqlSb.append("and t3.custom_no like ? ");
			list.add("%" + cur_custom_no + "%");
		}
		if (!StringUtils.isBlank(cur_custom_name)) {
			sqlSb.append("and t3.name like ? ");
			list.add("%" + cur_custom_name + "%");
		}
		if (!StringUtils.isBlank(cur_custom_login_id)) {
			sqlSb.append("and t3.login_id like ? ");
			list.add("%" + cur_custom_login_id + "%");
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sqlSb.append("and t2.ins_cd like ? ");
			list.add("%" + ins_cd + "%");
		}
		if (!StringUtils.isBlank(ins_name)) {
			sqlSb.append("and t2.ins_cn_nm like ? ");
			list.add("%" + ins_name + "%");
		}
		
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}

	/**
	 * 运营商附加收益汇总列表数据查询
	 *
	 * @param request
	 * @param pagination
	 * @param insAccountChangeEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param ins_name
	 * @param ins_cd
	 * @return
	 */
	public Map getInsAccountChangeListSum(HttpServletRequest request, Pagination pagination,
									   App_Ins_Account_ChangeEntity insAccountChangeEntity, String loc_dt_start, String loc_dt_end,
										  String ins_cd, String ins_name) {
		JSONObject common = getQuerySqlAndParams(loc_dt_start, loc_dt_end, ins_cd, ins_name);
		pagination.setQueryString(common.getString("sql"));
		pagination = super.getInsAuthSql(request, pagination, "t1", null);
		pagination.queryString = pagination.queryString + " group by loc_dt  order by t1.loc_dt desc";
		return insAccountChangeDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));
	}

	/**
	 * 导出数据
	 *
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param insAccountChangeEntity
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param ins_cd
	 * @param ins_name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String exportInsAccountChangeListsSum(String fileName, HttpServletRequest request, HttpServletResponse response,
											  Pagination pagination, App_Ins_Account_ChangeEntity insAccountChangeEntity, String loc_dt_start,
											  String loc_dt_end, String ins_cd,
											  String ins_name) {
		JSONObject common = getQuerySqlAndParams(loc_dt_start, loc_dt_end, ins_cd, ins_name);
		String sql = super.getInsAuthSql(request, common.getString("sql"), "t1", null);
		sql = sql + " group by loc_dt order by t1.loc_dt desc";
		List<Map> resultList = insAccountChangeDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("变动日期");
		headList.add("机构（收益人）");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("变动金额");
		headList.add("备注");
		return ExcelUtil.writeExcel(fileName, "机构账户变动明细", headList, resultList);
	}

	private JSONObject getQuerySqlAndParams(String loc_dt_start, String loc_dt_end, String ins_cd, String ins_name) {
		StringBuffer sqlSb = new StringBuffer();
		List<String> list = new ArrayList<>();
		sqlSb.append("select max(change_uuid) as uuid, ");
		sqlSb.append("max(TO_CHAR(TO_DATE(t1.loc_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd')) as loc_dt, ");
		sqlSb.append("max(t2.ins_cd||'-'||t2.ins_cn_nm) as ins_name, ");
		sqlSb.append("max(t1.service_no||'-'||t1.service_name) as service_name, ");
		sqlSb.append("max(t1.param_no||'-'||t1.param_name) as service_param_name, ");
		sqlSb.append("to_char(sum(t1.amount),'fm9999999990.00') as amount , ");
		sqlSb.append("max(t1.remarks) as remarks ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_INS_ACCOUNT_CHANGE + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_uuid = t2.ins_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t3 ");
		sqlSb.append("on t1.cur_custom_uuid = t3.custom_uuid ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(loc_dt_start)) {
			Date date = DateUtil.strToDate(loc_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.loc_dt >= ? ");
			list.add(DateUtil.getAddDaysDate(new Date(), -31));
		}
		if (!StringUtils.isBlank(loc_dt_end)) {
			Date date = DateUtil.strToDate(loc_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}  else {
			sqlSb.append(" and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sqlSb.append("and t2.ins_cd like ? ");
			list.add("%" + ins_cd + "%");
		}
		if (!StringUtils.isBlank(ins_name)) {
			sqlSb.append("and t2.ins_cn_nm like ? ");
			list.add("%" + ins_name + "%");
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}

	/**
	 * 获取汇总数据
	 * @param request
	 * @param loc_dt_start
	 * @param loc_dt_end
	 * @param ins_cd
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map getInsAccountChangeTotal(HttpServletRequest request, App_Ins_Account_ChangeEntity insAccountChangeEntity,
									   String loc_dt_start, String loc_dt_end,
									   String ins_cd,String ins_name) {
		StringBuffer sqlSb = new StringBuffer();
		List list = new ArrayList();
		sqlSb.append(" select sum(t1.amount) as all_amt ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_INS_ACCOUNT_CHANGE + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_uuid = t2.ins_uuid ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(loc_dt_start)) {
			Date date = DateUtil.strToDate(loc_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else {
			sqlSb.append(" and t1.loc_dt >= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(loc_dt_end)) {
			Date date = DateUtil.strToDate(loc_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.loc_dt <= ? ");
			list.add(DateUtil.dateToStr(DateUtil.StringToDate(DateUtil.getPreDate()), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(ins_cd)) {
			sqlSb.append("and t2.ins_cd like ? ");
			list.add("%" + ins_cd + "%");
		}
		if (!StringUtils.isBlank(ins_name)) {
			sqlSb.append("and t2.ins_cn_nm like ? ");
			list.add("%" + ins_name + "%");
		}
		String sql = sqlSb.toString();
		sql = this.getInsAuthSql(request, sql, "t1", null);
		log.debug("getTransactionLogList queryString: " + sql);
		return insAccountChangeDao.queryForMap(sql, list);
	}
}
